﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;
using MediaPortal.Configuration;
using MediaPortal.Database;
using MediaPortal.GUI.Library;

namespace MyEmulators2
{
    public class DB
    {
        const string DB_FILE_NAME = "Emulators2_v1.db3";
        const double DB_VERSION = 0.2;

        #region Singleton

        static object instanceSync = new object();
        static DB instance = null;
        public static DB Instance
        {
            get
            {
                if (instance == null)
                    lock (instanceSync)
                        if (instance == null)
                            instance = new DB();
                return instance;
            }
        }

        #endregion

        public DB()
        {
            init();
        }
        
        SQLiteClient sqlClient = null;
        bool isInit = false;

        void init()
        {
            lock (syncRoot)
            {
                if (isInit)
                    return;

                sqlClient = new SQLiteClient(Config.GetFile(Config.Dir.Database, DB_FILE_NAME));

                DatabaseUtility.AddTable(sqlClient, Emulator.TABLE_NAME, Emulator.DBTableString);
                DatabaseUtility.AddTable(sqlClient, EmulatorProfile.TABLE_NAME, EmulatorProfile.DBTableString);
                DatabaseUtility.AddTable(sqlClient, Game.TABLE_NAME, Game.DBTableString);

                DatabaseUtility.AddTable(sqlClient, "Info",
                    "CREATE TABLE Info(" +
                        "name varchar(50)," +
                        "value varchar(100)," +
                        "PRIMARY KEY(name)" +
                    ")"
                );

                isInit = true;
            }
        }

        public void UpdateDatabase()
        {
            lock (syncRoot)
                new DB_Updater().Update();
        }

        protected readonly object syncRoot = new object();
        public object SyncRoot
        {
            get { return syncRoot; }
        }

        public SQLiteResultSet Execute(string query, params object[] args)
        {
            lock (syncRoot)
                return sqlClient.Execute(string.Format(System.Globalization.CultureInfo.InvariantCulture, query, args));
        }

        public SQLiteResultSet ExecuteWithoutLock(string query, params object[] args)
        {
            return sqlClient.Execute(string.Format(System.Globalization.CultureInfo.InvariantCulture, query, args));
        }

        public double CurrentDBVersion
        {
            get
            {
                SQLiteResultSet result = Execute("SELECT value FROM Info WHERE name='version'");
                if (result.Rows.Count == 0)
                    return DB_VERSION; //Fresh install
                else
                    return double.Parse(result.Rows[0].fields[0], System.Globalization.CultureInfo.InvariantCulture);
            }
        }

        public void UpdateDBVersion()
        {
            lock (syncRoot)
            {
                SQLiteResultSet result = ExecuteWithoutLock("SELECT value FROM Info WHERE name='version'");
                string query;
                if (result.Rows.Count == 0)
                    query = "INSERT INTO Info VALUES('version','{0}')";
                else
                    query = "UPDATE Info SET value='{0}' WHERE name='version'";

                ExecuteWithoutLock(query, DB_VERSION);
            }
        }

        #region Emulators

        public Emulator GetEmulator(int uid)
        {
            if (uid == -1)
                return Emulator.GetPC();

            SQLiteResultSet result = Execute("SELECT * FROM {0} WHERE uid={1}", Emulator.TABLE_NAME, uid);
            if (result.Rows.Count < 1)
            {
                Logger.LogError("Unable to locate Emulator with uid {0}", uid);
                return null;
            }

            return Emulator.CreateEmulator(result.Rows[0]);
        }

        public Emulator[] GetEmulators()
        {
            List<Emulator> emus = new List<Emulator>();

            SQLiteResultSet result = Execute("SELECT * FROM {0} ORDER BY position ASC", Emulator.TABLE_NAME);
            foreach (SQLiteResultSet.Row row in result.Rows)
                emus.Add(Emulator.CreateEmulator(row));

            return emus.ToArray();
        }

        public Emulator[] GetEmulatorsAndPC()
        {
            List<Emulator> emus = new List<Emulator>();
            emus.AddRange(GetEmulators());

            Emulator pc = Emulator.GetPC();
            if (GetGames(pc).Length > 0)
            {
                //sanitise position just in case
                if (pc.Position < 0)
                    pc.Position = 0;
                else if (pc.Position > emus.Count)
                    pc.Position = emus.Count;
                emus.Insert(pc.Position, pc); //Insert PC into correct position in list
            }

            return emus.ToArray();
        }

        #endregion

        #region Games

        public List<string> GetAllGamePaths()
        {
            List<string> paths = new List<string>();

            SQLiteResultSet result = Execute("SELECT path FROM {0}", Game.TABLE_NAME);
            foreach (SQLiteResultSet.Row row in result.Rows)
                paths.Add(Decode(row.fields[0]));

            return paths;
        }

        /// <summary>
        /// Adds a game with the specified path and parent emulator to the database. If the path is already
        /// in the database no modifications are made and the database game is simply returned.
        /// </summary>
        /// <param name="path">The path to the game to add</param>
        /// <param name="parentEmu">The parent emulator for the game</param>
        /// <returns></returns>
        public void AddGame(string path, Emulator parentEmu)
        {
            lock (syncRoot)
            {
                SQLiteResultSet result = ExecuteWithoutLock("SELECT * FROM {0} WHERE path='{1}'", Game.TABLE_NAME, Encode(path));
                if (result.Rows.Count < 1)
                {
                    Game game = new Game(path, parentEmu);
                    game.Save();
                }
            }
        }

        /// <summary>
        /// Returns the game with the specified id. If no database entry is found
        /// this returns null.
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Game GetGame(int id)
        {
            Game[] games = GetGames("WHERE gameid=" + id);
            if (games.Length < 1)
            {
                Logger.LogError("Unable to locate Game with id {0}", id);
                return null;
            }

            return games[0];
        }

        /// <summary>
        /// Returns all games in the database.
        /// </summary>
        /// <returns></returns>
        public Game[] GetGames()
        {
            return GetGames("");
        }

        /// <summary>
        /// Returns all games belonging to the specified emulator.
        /// </summary>
        /// <param name="emu"></param>
        /// <returns></returns>
        public Game[] GetGames(Emulator emu)
        {
            if(emu == null)
                return new Game[0];

            return GetGames("WHERE parentemu=" + emu.UID);
        }

        /// <summary>
        /// Returns all games selected by the specified SQL Query.
        /// The default 'ORDER BY' clause will be automatically appended. 
        /// </summary>
        /// <param name="sqlAppend">The 'WHERE' clause to append to the internal 'SELECT' statement.</param>
        /// <returns></returns>
        public Game[] GetGames(string sqlAppend)
        {
            return GetGames(sqlAppend, true, false);
        }

        /// <summary>
        /// Returns all games selected by the specified SQL Query.
        /// If <paramref name="defaultSort"/> is false then <paramref name="sqlAppend"/>
        /// should contain an 'ORDER BY' clause.
        /// </summary>
        /// <param name="sqlAppend">The 'WHERE' clause to append to the internal 'SELECT' statement.</param>
        /// <param name="defaultSort">
        /// Whether the default 'ORDER BY' clause should be appended to the query.
        /// Default True.
        /// </param>
        /// <returns></returns>
        public Game[] GetGames(string sqlAppend, bool defaultSort)
        {
            return GetGames(sqlAppend, defaultSort, false);
        }

        public Game[] GetGames(string sqlAppend, bool defaultSort, bool showOrphans)
        {
            List<Game> games = new List<Game>();

            string query = "SELECT * FROM " + Game.TABLE_NAME;

            if (!string.IsNullOrEmpty(sqlAppend))
                query += " " + sqlAppend;

            if (defaultSort)
                query += " ORDER BY parentemu, title";

            SQLiteResultSet result = Execute(query);
            foreach (SQLiteResultSet.Row row in result.Rows)
            {
                Game game = Game.CreateGame(row);
                if (game != null)
                    games.Add(game);
                else if (showOrphans)
                    games.Add(new Game(Decode(row.fields[1]), null) { GameID = int.Parse(row.fields[0], System.Globalization.CultureInfo.InvariantCulture) });
            }

            return games.ToArray();
        }

        #endregion

        #region EmulatorProfiles

        public EmulatorProfile GetProfile(int id, Emulator parentEmu)
        {
            SQLiteResultSet result = Execute("SELECT * FROM {0} WHERE uid={1} AND emulator_id={2}", EmulatorProfile.TABLE_NAME, id, parentEmu.UID);
            if (result.Rows.Count > 0)
            {
                return EmulatorProfile.CreateProfile(result.Rows[0]);
            }

            return new EmulatorProfile(parentEmu, false);
        }

        public EmulatorProfile[] GetProfiles(Emulator emu)
        {
            List<EmulatorProfile> profiles = new List<EmulatorProfile>();

            profiles.Add(new EmulatorProfile(GetEmulator(emu.UID), false)); //default profile

            SQLiteResultSet result = Execute("SELECT * FROM {0} WHERE emulator_id={1}", EmulatorProfile.TABLE_NAME, emu.UID);
            foreach (SQLiteResultSet.Row row in result.Rows)
                profiles.Add(EmulatorProfile.CreateProfile(row));

            return profiles.ToArray();
        }

        #endregion

        #region Utils

        public static string Encode(String input)
        {
            if (input == null)
            {
                return null;
            }
            else
            {
                String encoded = input;
                encoded = encoded.Replace("'", "&#39;");
                encoded = encoded.Replace("\"", "&#34;");
                return encoded;
            }
        }

        public static string Decode(String input)
        {
            if (input == null)
            {
                return null;
            }
            else
            {
                String decoded = input;
                decoded = decoded.Replace("&#39;", "'");
                decoded = decoded.Replace("&#34;", "\"");
                return decoded;
            }
        }

        #endregion
    }
}
